CREATE PROCEDURE [dbo].[CreateBalanceSheet]
    @StartDate VARCHAR(10),
    @EndDate VARCHAR(10),
    @AccountLevel SMALLINT,
    @ContainParentLevel BIT,
    @StartAccountCode VARCHAR(20),
    @EndAccountCode VARCHAR(20),
    @StartNoteNumber FLOAT,
    @EndNoteNumber FLOAT
AS
DECLARE @ResultTable TABLE
(
    AccountCode VARCHAR(20),
    AccountName NVARCHAR(90),
    AccountLevel SMALLINT,
    Bedeh MONEY,
    Bestan MONEY,
    RemainBedeh MONEY,
    RemainBestan MONEY,
    StoreCode VARCHAR(3)
);

DECLARE @Sanad TABLE
(
    AccountCode VARCHAR(20),
    Bedeh MONEY,
    Bestan MONEY,
    StoreCode VARCHAR(3)
);
DECLARE @VarStartDate SMALLDATETIME = dbo.ShamsiToMiladi(@StartDate);
DECLARE @VarEndDate SMALLDATETIME = dbo.ShamsiToMiladi(@EndDate);

INSERT INTO @Sanad
SELECT Acc_Code,
       SUM(BEDEH) Bedeh,
       SUM(BESTAN) Bestan,
       ISNULL(Sanad.Store_Code, '')
FROM dbo.Sanad WITH (NOLOCK)
    INNER JOIN HeadS
        ON CAST(dbo.HeadS.Tarikh_User AS DATE)
           BETWEEN @VarStartDate AND @VarEndDate
           AND HeadS.Id_Sanad = Sanad.Id_Sanad
WHERE Nu_Manual
      BETWEEN @StartNoteNumber AND @EndNoteNumber
      AND Acc_Code
      BETWEEN @StartAccountCode AND @EndAccountCode
GROUP BY Acc_Code,
         Sanad.Store_Code;

DECLARE @Acc TABLE
(
    AccountCode VARCHAR(20),
    AccountName NVARCHAR(90),
    ParentCode INT,
    AutoId INT,
    AccountLevel SMALLINT,
    HasChild BIT,
    Bedeh MONEY,
    Bestan MONEY,
    StoreCode VARCHAR(3)
);
INSERT INTO @Acc
SELECT Account.Acc_Code,
       Account.Acc_Name,
       Account.Parent_Code,
       Account.AutoId,
       Account.Acc_Level,
       Account.Has_Child,
       Sanad.Bedeh,
       Sanad.Bestan,
       Sanad.StoreCode
FROM Account
    LEFT JOIN @Sanad Sanad
        ON Sanad.AccountCode = Account.Acc_Code
WHERE (
          Has_Child = 0
          AND
          (
              Sanad.Bedeh > 0
              OR Sanad.Bestan > 0
          )
      )
      OR (Has_Child = 1)
ORDER BY Acc_Code;
WITH CTE
AS (SELECT Acc.AutoId,
           Acc.ParentCode,
           Acc.AccountCode,
           Acc.AccountName,
           Acc.AccountLevel,
           CASE
               WHEN Acc.HasChild = 0 THEN
                   Acc.Bedeh
               ELSE
                   0
           END Bedeh,
           CASE
               WHEN Acc.HasChild = 0 THEN
                   Acc.Bestan
               ELSE
                   0
           END Bestan,
           Acc.HasChild,
           Acc.StoreCode
    FROM @Acc Acc
    UNION ALL
    SELECT Acc.AutoId,
           Acc.ParentCode,
           Acc.AccountCode,
           Acc.AccountName,
           Acc.AccountLevel,
           CTE.Bedeh,
           CTE.Bestan,
           Acc.HasChild,
           CTE.StoreCode
    FROM @Acc Acc
        JOIN CTE
            ON Acc.AutoId = CTE.ParentCode)
INSERT INTO @ResultTable
(
    AccountCode,
    AccountName,
    AccountLevel,
    Bedeh,
    Bestan,
    RemainBedeh,
    RemainBestan,
    StoreCode
)
SELECT AccountCode,
       AccountName,
       CTE.AccountLevel,
       SUM(Bedeh) Bedeh,
       SUM(Bestan) Bestan,
       CASE
           WHEN SUM(Bedeh - Bestan) > 0 THEN
               SUM(Bedeh - Bestan)
           ELSE
               0
       END,
       CASE
           WHEN SUM(Bestan - Bedeh) > 0 THEN
               SUM(Bestan - Bedeh)
           ELSE
               0
       END,
       CTE.StoreCode
FROM CTE
GROUP BY AccountCode,
         AccountName,
         StoreCode,
         CTE.AccountLevel
HAVING SUM(Bedeh) > 0
       OR SUM(Bestan) > 0
ORDER BY AccountCode;


DECLARE @SumBedeh MONEY,
        @SumBestan MONEY,
        @SumRemainBedeh MONEY,
        @SumRemainBestan MONEY;

-- select * from @Acc


SELECT @SumBedeh = SUM(Result.Bedeh),
       @SumBestan = SUM(Result.Bestan),
       @SumRemainBedeh = SUM(RemainBedeh),
       @SumRemainBestan = SUM(RemainBestan)
FROM @ResultTable Result
    INNER JOIN Account
        ON Account.Acc_Code = Result.AccountCode
WHERE (
          @ContainParentLevel = 1
          AND AccountLevel = 1
      )
      OR
      (
          @ContainParentLevel = 0
          AND AccountLevel = @AccountLevel
      );
-- select * from @ResultTable

SELECT R.*,
       @SumBedeh SumBedeh,
       @SumBestan SumBestan,
       @SumRemainBedeh SumRemainBedeh,
       @SumRemainBestan SumRemainBestan
FROM @ResultTable R
WHERE (
          (
              @ContainParentLevel = 0
              AND AccountLevel = @AccountLevel
          )
          OR
          (
              @ContainParentLevel = 1
              AND AccountLevel <= @AccountLevel
          )
      );